Defect Location Analysis For Instrument Protector Cards (Data from March, 2023)¶

In [1]:
import numpy as np
import pandas as pd
from PIL import Image
import plotly.express as px
import plotly.graph_objects as go
import pyodbc as odbc
import plotly.express as px
import duckdb as db
import warnings
import dtale as dtl
warnings.filterwarnings("ignore")

Defect Location Analysis For Small Card (Data from March, 2023)¶

Identifying Defects Locations since March 2023 to answer which is the most common location of defect for Small Cards¶

In [2]:
# conn = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};''SERVER=XYZ;\
#                     ''DATABASE=XYZ;''Trusted_Connection=yes;')
# dframe = pd.read_sql_query("SELECT * FROM XYZ", conn)
# dframe.to_csv('temp_defect_summary.csv')

dframe = pd.read_csv('temp_defect_summary.csv')
In [3]:
import plotly.express as px

df = dframe
df = df.sort_values(by='measured_date')
fig = px.line(df, x="measured_date", y="defect_count", color='defect_location_category')
fig.show()
In [4]:
device_coordinate_map = {
    'lower-left': [0.2, 0.2],
    'mid-left': [0.2, 0.5],
    'upper-left': [0.2, 0.8],
    'lower-mid': [0.5, 0.2],
    'mid': [0.5, 0.5],
    'upper-mid': [0.5, 0.8],
    'lower-right': [0.8, 0.2],
    'mid-right': [0.8, 0.5],
    'upper-right': [0.8, 0.8]
}

dt_lap = dframe.query("defect_location_category != 'misallgined'")
dt_lap["time_string"] = dt_lap["measured_date"].astype(str)
dt_lap['x'], dt_lap['y'] = zip(*list(dt_lap['defect_location_category'].map(device_coordinate_map).values))

Change in Overall Defects Locations since March 2023 with Time Scroll¶

In [5]:
img = Image.open('SmallCard.JPG')

# Create figure
fig = px.scatter(dt_lap, x="x", y="y",
                 size="defect_count", color="defect_location_category",animation_frame="time_string",\
                 animation_group="defect_location_category", height=425, width=800, hover_name="measured_date")

# Add images
fig.add_layout_image(
    dict(
        source=img,
        x=0,
        y=1,
        sizex=1,
        sizey=1,
        sizing="contain",
        layer="below"
    )
)

# Set templates
fig.update_layout(
    template="plotly_white", 
    xaxis=dict(range=[0,1], showgrid=False),
    yaxis=dict(range=[0,1], showgrid=False)
)

fig.show()

Overall Defects Location since March 2023 with Heatmap Visualization for better Understanding¶

In [6]:
dt_lap_grouped = dt_lap[['defect_location_category','defect_count','x','y']]
dt_lap_grouped = dt_lap_grouped.groupby(by='defect_location_category').agg({'defect_count':'sum','x':'max','y':'max'})\
                .reset_index()
In [7]:
img = Image.open('SmallCard.JPG')
# Create figure
fig = px.scatter(dt_lap_grouped, x="x", y="y",
                 size="defect_count", color="defect_location_category", height=350, width=800)\
      .update_traces(marker=dict(color='red'))

# Add images
fig.add_layout_image(
    dict(
        source=img,
        x=0,
        y=1,
        sizex=1,
        sizey=1,
        sizing="contain",
        layer="below"
    )
)

# Set templates
fig.update_layout(
    template="plotly_white", 
    xaxis=dict(range=[0,1], showgrid=False),
    yaxis=dict(range=[0,1], showgrid=False)
)

fig.show()
In [8]:
from sklearn.preprocessing import MinMaxScaler
dt_lap_grouped['defect_count_scaled'] = MinMaxScaler((0.1,0.85))\
                                        .fit_transform(np.array(dt_lap_grouped['defect_count']).reshape(-1,1))

device_coordinate_map_rect = {
    'lower-left': [0, 0.33, 0, 0.307],
    'mid-left': [0, 0.33, 0.307, 0.641],
    'upper-left': [0, 0.33, 0.641, 1],
    
    'lower-mid': [0.33, 0.65625, 0, 0.307],
    'mid': [0.33, 0.65625, 0.307, 0.641],
    'upper-mid': [0.33, 0.65625, 0.641, 1],
    
    'lower-right': [0.65625, 1, 0, 0.307],
    'mid-right': [0.65625, 1, 0.307, 0.641],
    'upper-right': [0.65625, 1, 0.641, 1],
}

dt_lap_grouped['x0'], dt_lap_grouped['x1'], dt_lap_grouped['y0'], dt_lap_grouped['y1'] = \
                                                     zip(*list(dt_lap_grouped['defect_location_category']\
                                                     .map(device_coordinate_map_rect).values))
In [9]:
img = Image.open('SmallCard.JPG')
# Create figure
fig = px.scatter(dt_lap_grouped, x="x", y="y", size="defect_count",
                 hover_data={"defect_count":True, "x":False, "y":False, 'defect_location_category':False}, 
                 color="defect_location_category", height=350, width=686)\
      .update_traces(marker=dict(color='black'))

for i in dt_lap_grouped.values:
    fig.add_vrect(x0=i[5], x1=i[6], y0=i[7], y1=i[8], fillcolor="red", opacity=i[4])

# Add images
fig.add_layout_image(
    dict(
        source=img,
        x=0,
        y=1,
        sizex=1,
        sizey=1,
        sizing="contain",
        layer="below"
    )
)

# Set templates
fig.update_layout(
    template="plotly_white", 
    xaxis=dict(range=[0,1], showgrid=False),
    yaxis=dict(range=[0,1], showgrid=False),
    showlegend=False
)

fig.add_hline(y=0, line_color="red")
fig.add_hline(y=0.307, line_color="red")
fig.add_hline(y=0.641, line_color="red")
fig.add_vline(x=0, line_color="red")
fig.add_vline(x=0.33, line_color="red")
fig.add_vline(x=0.65625, line_color="red")

fig.show()

Other than defect locations, how many cards were rejected due to Mis-Allignment?¶

In [10]:
temp = db.sql("SELECT defect_location_category, SUM(defect_count) defect_count, \
        FROM dframe GROUP BY defect_location_category").df()

miaslligned_percent = temp[temp['defect_location_category']=='misallgined']['defect_count'].sum()/\
                            temp[temp['defect_location_category']!='misallgined']['defect_count'].sum()

print(f'Overall {np.round(miaslligned_percent*100,2)}% Cards of Size 2 were rejected due to misallignment')

Overall 5.07% Cards of Size 2 were rejected due to misallignment

Defect Location Analysis For Medium Card (Data from March, 2023)¶

In [11]:
dframe_cs1 = pd.read_csv('temp_defect_summary_medium.csv')
In [13]:
#dframe_cs1['defect_location_category'] = 

dframe_cs1 = \
        db.sql("SELECT measurementtime, loc_x, loc_y,\
            CASE WHEN (loc_x=0 and loc_y=0) then 'misallgined'\
            WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>=-200 and loc_y<=116) then 'lower-left'\
            WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>116 and loc_y<=434) then 'mid-left'\
            WHEN (loc_x>=-50 and loc_x<=383) and (loc_y>434 and loc_y<=750) then 'upper-left'\
            WHEN (loc_x>383 and loc_x<=817) and (loc_y>=-200 and loc_y<=116) then 'lower-mid'\
            WHEN (loc_x>383 and loc_x<=817) and (loc_y>116 and loc_y<=434) then 'mid'\
            WHEN (loc_x>383 and loc_x<=817) and (loc_y>434 and loc_y<=750) then 'upper-mid'\
            WHEN (loc_x>817 and loc_x<=1250) and (loc_y>=-200 and loc_y<=116) then 'lower-right'\
            WHEN (loc_x>817 and loc_x<=1250) and (loc_y>116 and loc_y<=434) then 'mid-right'\
            WHEN (loc_x>817 and loc_x<=1250) and (loc_y>434 and loc_y<=750) then 'upper-right'\
            ELSE 'misallgined'\
            END AS defect_location_category\
       FROM dframe_cs1").df()

dframe_cs1['measurementtime'] = pd.to_datetime(dframe_cs1['measurementtime'])
dframe_cs1['measured_date'] = dframe_cs1.measurementtime.dt.date

dframe_cs1_summ_daily = dframe_cs1[['measured_date','defect_location_category']]\
                        .groupby(by=['measured_date','defect_location_category'])\
                        .agg(defect_count = ('measured_date', 'count')).reset_index()

Identifying Defects Locations since March 2023 to answer which is the most common location of defect for Medium Cards¶

In [14]:
dframe_cs1_summ_daily = dframe_cs1_summ_daily.sort_values(by='measured_date')
fig = px.line(dframe_cs1_summ_daily, x="measured_date", y="defect_count", color='defect_location_category')
fig.show()

Overall Defects Location since March 2023 with Heatmap Visualization for better Understanding¶

In [15]:
device_coordinate_map = {
    'lower-left': [0.2, 0.2],
    'mid-left': [0.2, 0.5],
    'upper-left': [0.2, 0.8],
    'lower-mid': [0.5, 0.2],
    'mid': [0.5, 0.5],
    'upper-mid': [0.5, 0.8],
    'lower-right': [0.8, 0.2],
    'mid-right': [0.8, 0.5],
    'upper-right': [0.8, 0.8]
}

dt_lap = dframe_cs1_summ_daily.query("defect_location_category != 'misallgined'")
dt_lap["time_string"] = dt_lap["measured_date"].astype(str)
dt_lap['x'], dt_lap['y'] = zip(*list(dt_lap['defect_location_category'].map(device_coordinate_map).values))

dt_lap_grouped = dt_lap[['defect_location_category','defect_count','x','y']]
dt_lap_grouped = dt_lap_grouped.groupby(by='defect_location_category').agg({'defect_count':'sum','x':'max','y':'max'})\
                .reset_index()

from sklearn.preprocessing import MinMaxScaler
dt_lap_grouped['defect_count_scaled'] = MinMaxScaler((0.1,0.85))\
                                        .fit_transform(np.array(dt_lap_grouped['defect_count']).reshape(-1,1))


device_coordinate_map_rect = {
    'lower-left': [0, 0.33, 0, 0.307],
    'mid-left': [0, 0.33, 0.307, 0.641],
    'upper-left': [0, 0.33, 0.641, 1],
    
    'lower-mid': [0.33, 0.65625, 0, 0.307],
    'mid': [0.33, 0.65625, 0.307, 0.641],
    'upper-mid': [0.33, 0.65625, 0.641, 1],
    
    'lower-right': [0.65625, 1, 0, 0.307],
    'mid-right': [0.65625, 1, 0.307, 0.641],
    'upper-right': [0.65625, 1, 0.641, 1],
}

dt_lap_grouped['x0'], dt_lap_grouped['x1'], dt_lap_grouped['y0'], dt_lap_grouped['y1'] = \
                                                     zip(*list(dt_lap_grouped['defect_location_category']\
                                                     .map(device_coordinate_map_rect).values))

img = Image.open('MedCard.JPG')


fig = px.scatter(dt_lap_grouped, x="x", y="y", size="defect_count",
                 hover_data={"defect_count":True, "x":False, "y":False, 'defect_location_category':False}, 
                 color="defect_location_category", height=360, width=578)\
      .update_traces(marker=dict(color='black'))

for i in dt_lap_grouped.values:
    fig.add_vrect(x0=i[5], x1=i[6], y0=i[7], y1=i[8], fillcolor="red", opacity=i[4])

# Add images
fig.add_layout_image(
    dict(
        source=img,
        x=0,
        y=1,
        sizex=1,
        sizey=1,
        sizing="contain",
        layer="below"
    )
)

# Set templates
fig.update_layout(
    template="plotly_white", 
    xaxis=dict(range=[0,1], showgrid=False),
    yaxis=dict(range=[0,1], showgrid=False),
    showlegend=False
)

fig.add_hline(y=0, line_color="red")
fig.add_hline(y=0.307, line_color="red")
fig.add_hline(y=0.641, line_color="red")
fig.add_vline(x=0, line_color="red")
fig.add_vline(x=0.33, line_color="red")
fig.add_vline(x=0.65625, line_color="red")

fig.show()

Other than defect locations, how many cards were rejected due to Mis-Allignment?¶

In [16]:
temp = db.sql("SELECT defect_location_category, SUM(defect_count) defect_count, \
        FROM dframe_cs1_summ_daily GROUP BY defect_location_category").df()

miaslligned_percent = temp[temp['defect_location_category']=='misallgined']['defect_count'].sum()/\
                            temp[temp['defect_location_category']!='misallgined']['defect_count'].sum()

print(f'Overall {np.round(miaslligned_percent*100,2)}% Cards of Size 1 were rejected due to misallignment')

Overall 2.04% Cards of Size 1 were rejected due to misallignment